/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.aptech.childcare.dao;

import com.aptech.childcare.entities.Agegroup;
import com.aptech.childcare.until.ConfigDatabase;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author Xuan Hieu
 */
public class AgegroupDAO {

    private String lastError;
    private Connection con = null;
    private ConfigDatabase db = null;
    private PreparedStatement pst = null;
    private ResultSet rs = null;
    private final String sqlCREATE = "INSERT INTO tbAge_group VALUES(?,?,?)";
    private final String sqlREAD = "SELECT * FROM tbAge_group";
    private final String sqlUPDATE = "SELECT * FROM tbAge_group WHERE AgegroupID = ?";
    private final String sqlDELETE = "DELETE FROM tbAge_group WHERE AgegroupID = ?";

    public AgegroupDAO() {
        db = new ConfigDatabase();
    }

    /**
     *
     * @param ag the Agegroup needed create
     * @return true if create successful, false if create fail
     */
    public boolean create(Agegroup age) throws IOException {
        if (!exist(age)) {
            return false;
        }
        try {
            con = db.getConnection();
            pst = con.prepareStatement(sqlCREATE);
            pst.setString(1, age.getAgeGroup());
            pst.setFloat(2, age.getFees());
            pst.setString(3, age.getNote());
            if (pst.executeUpdate() > 0) {
                setLastError("Add successful!");
                return true;
            }
        } catch (SQLException ex) {
            setLastError("SQL fail!");
        } finally {
            db.closeConnection(db);
        }
        setLastError("Add fail!");
        return false;
    }

    /**
     * 
     * @param age
     * @return
     */
    public boolean exist(Agegroup age) throws IOException {
        ArrayList<Agegroup> list = readByAll();
        for (Agegroup ag : list) {
            if (age.getAgeGroup().equals(ag.getAgeGroup())) {
                setLastError("Agegroup existed !");
                return false;
            }
        }
        return true;
    }

    /**
     *
     * @param ageGroupID the Agegroup needed delete
     * @return true if delete successful, false if delete fail
     */
    public boolean delete(int ageGroupID) throws IOException {
        try {
            con = db.getConnection();
            pst = con.prepareStatement(sqlDELETE);
            pst.setInt(1, ageGroupID);
            if (pst.executeUpdate() > 0) {
                setLastError("Delete successful!");
                return true;
            }
        } catch (SQLException ex) {
            setLastError("SQL fail!");
        } finally {
            db.closeConnection(db);
        }
        setLastError("Delete fail!");
        return false;
    }

    public Agegroup readByID(int AgegroupID) throws IOException {
        Agegroup age = new Agegroup();
        con = db.getConnection();
        try {
            pst = con.prepareStatement(sqlUPDATE);
            pst.setInt(1, AgegroupID);
            rs = pst.executeQuery();
            while (rs.next()) {
                age.setAgeGroupID(rs.getInt(1));
                age.setAgeGroup(rs.getString(2));
                age.setNote(rs.getString(3));
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            db.closeConnection(db);
        }
        return age;
    }

    /**
     *
     * @param ag the Agegroup needed update
     * @return true if update successful, false if update fail
     */
    public boolean update(Agegroup age) throws IOException {
        con = db.getConnection();
        try {
            pst = con.prepareStatement(sqlUPDATE, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            pst.setInt(1, age.getAgeGroupID());
            rs = pst.executeQuery();
            rs.first();
            rs.updateString(2, age.getAgeGroup());
            rs.updateFloat(3, age.getFees());
            rs.updateString(4, age.getNote());
            rs.updateRow();
            setLastError("Update successfully!");
            return true;
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            db.closeConnection(db);
        }
        setLastError("Update Fail!");
        return false;
    }

    /**
     * 
     * @return
     */
    public ArrayList<Agegroup> readByAll() throws IOException {
        ArrayList<Agegroup> list = new ArrayList<Agegroup>();
        con = db.getConnection();
        try {
            pst = con.prepareStatement(sqlREAD);
            rs = pst.executeQuery();
            while (rs.next()) {
                Agegroup age = new Agegroup();
                age.setAgeGroupID(rs.getInt(1));
                age.setAgeGroup(rs.getString(2));
                age.setFees(rs.getFloat(3));
                age.setNote(rs.getString(4));
                list.add(age);
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            db.closeConnection(db);
        }
        return list;
    }

    /**
     *
     * @param name the AgeGroup needed search
     * @return list of agegroup
     */
    public ArrayList<Agegroup> readByName(String name) throws IOException {
        ArrayList<Agegroup> list = readByAll();
        ArrayList<Agegroup> ageList = new ArrayList<Agegroup>();
        for (Agegroup age : list) {
            if (age.getAgeGroup().toLowerCase().matches(".*" + name.trim().toLowerCase() + ".*")) {
                ageList.add(age);
            }
        }
        return ageList;
    }

    /**
     * @return the lastError
     */
    public String getLastError() {
        return lastError;
    }

    /**
     * @param lastError the lastError to set
     */
    public void setLastError(String lastError) {
        this.lastError = lastError;
    }
}
